package com.hanhai.zrb.api.biz.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * poi操作excel文件工具类
 * 
 * @author lc
 * @date 2015年3月10日
 */
public class XlsUtils {

	/**
	 * 对外提供读取excel 的方法
	 */
	public static List<List<Object>> readExcel(InputStream is, String extend, int begin, int columns)
			throws IOException {
		if ("xls".equalsIgnoreCase(extend)) {
			return read2003Excel(is, begin, columns);
		} else if ("xlsx".equalsIgnoreCase(extend)) {
			return read2007Excel(is, begin, columns);
		} else {
			throw new IOException("不支持的文件类型");
		}
	}

	/**
	 * 读取 office 2003 excel
	 * 
	 * @throws IOException
	 * @throws FileNotFoundException
	 */
	private static List<List<Object>> read2003Excel(InputStream is,int begin, int columns)
			throws IOException {
		List<List<Object>> list = new LinkedList<List<Object>>();
		HSSFWorkbook hwb = new HSSFWorkbook(is);
		HSSFSheet sheet = hwb.getSheetAt(0);
		HSSFRow row = null;
		HSSFCell cell = null;
		// for (int i = sheet.getFirstRowNum(); counter <
		// sheet.getPhysicalNumberOfRows(); i++) {
		for (int i = begin; i < sheet.getPhysicalNumberOfRows(); i++) {
			//System.out.println("i--"+i);
			row = sheet.getRow(i);
			if (row != null) {
				List<Object> linked = new LinkedList<Object>();
				for (int j = row.getFirstCellNum(); j <= columns; j++) {
					cell = row.getCell(j);
					if (cell != null) {
						boolean isMerge = isMergedRegion(sheet, i, cell.getColumnIndex()); 
						Object value = null;
						if(isMerge) { 
							value  = getMergedRegionValue(sheet, row.getRowNum(), cell.getColumnIndex()); 		                     
						}else{ 
							SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");// 格式化日期字符串
							switch (cell.getCellType()) {
							case XSSFCell.CELL_TYPE_STRING:
								value = cell.getStringCellValue();
								break;
							case XSSFCell.CELL_TYPE_NUMERIC:
								if ("@".equals(cell.getCellStyle().getDataFormatString())) {
									value = String.valueOf(cell.getNumericCellValue());
								} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
									value = String.valueOf(cell.getNumericCellValue());
								} else if ("m/d/yy".equalsIgnoreCase(cell.getCellStyle().getDataFormatString())){
									value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
								}
								break;
							case XSSFCell.CELL_TYPE_FORMULA:
								//value = cell.getRawValue();
								value = cell.getCellFormula();
								break;
							case XSSFCell.CELL_TYPE_BOOLEAN:
								value = cell.getBooleanCellValue();
								break;
							case XSSFCell.CELL_TYPE_BLANK:
								value = "";
								break;
							default:
								value = cell.toString();
							}
						} 
						if (value == null || "".equals(value)) {
							value = "";
						}
						linked.add(value);
					}
				}
				list.add(linked);
			}
		}
		return list;
	}

	/**
	 * 读取Office 2007 excel
	 */
	private static List<List<Object>> read2007Excel(InputStream is,int begin, int columns)
			throws IOException {
		List<List<Object>> list = new LinkedList<List<Object>>();
		// 构造 XSSFWorkbook 对象，strPath 传入文件路径
		XSSFWorkbook xwb = new XSSFWorkbook(is);
		// 读取第一章表格内容
		XSSFSheet sheet = xwb.getSheetAt(0);
		//Object value = null;
		XSSFRow row = null;
		XSSFCell cell = null;
		for (int i = begin; i < sheet.getPhysicalNumberOfRows(); i++) {
			// for (int i = 1; counter < sheet.getPhysicalNumberOfRows(); i++) {
			row = sheet.getRow(i);
			if (row != null) {
				List<Object> linked = new LinkedList<Object>();
				for (int j = row.getFirstCellNum(); j <= columns; j++) {
					cell = row.getCell(j);
					if (cell != null) {
						boolean isMerge = isMergedRegion(sheet, i, cell.getColumnIndex()); 
						Object value = null;
						if(isMerge) { 
							value = getMergedRegionValue(sheet, row.getRowNum(), cell.getColumnIndex()); 		                     
						}else{ 
							SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");// 格式化日期字符串
							switch (cell.getCellType()) {
							case XSSFCell.CELL_TYPE_STRING:
								value = cell.getStringCellValue();
								break;
							case XSSFCell.CELL_TYPE_NUMERIC:
								if ("@".equals(cell.getCellStyle().getDataFormatString())) {
									value = String.valueOf(cell.getNumericCellValue());
								} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
									value = String.valueOf(cell.getNumericCellValue());
								} else if ("m/d/yy".equalsIgnoreCase(cell.getCellStyle().getDataFormatString())){
									value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
								}
								break;
							case XSSFCell.CELL_TYPE_FORMULA:
								//value = cell.getRawValue();
								value = cell.getCellFormula();
								break;
							case XSSFCell.CELL_TYPE_BOOLEAN:
								value = cell.getBooleanCellValue();
								break;
							case XSSFCell.CELL_TYPE_BLANK:
								value = "";
								break;
							default:
								value = cell.toString();
							}
						} 
						if (value == null || "".equals(value)) {
							value = "";
						}
						linked.add(value);
					}
				}
				list.add(linked);
			}
		}
		return list;
	}
	
	 /**    
     * 获取合并单元格的值    
     * @param sheet    
     * @param row    
     * @param column    
     * @return    
     */     
     public static String getMergedRegionValue(Sheet sheet ,int row , int column){     
         int sheetMergeCount = sheet.getNumMergedRegions();     
         for(int i = 0 ; i < sheetMergeCount ; i++){     
             CellRangeAddress ca = sheet.getMergedRegion(i);     
             int firstColumn = ca.getFirstColumn();     
             int lastColumn = ca.getLastColumn();     
             int firstRow = ca.getFirstRow();     
             int lastRow = ca.getLastRow();     
             if(row >= firstRow && row <= lastRow){     
                  if(column >= firstColumn && column <= lastColumn){     
                     Row fRow = sheet.getRow(firstRow); 
                     Cell fCell = fRow.getCell(firstColumn); 
                     return getCellValue(fCell) ; 
                 }     
             }     
         }     
         return null ;     
     }   
     
     /**   
      * 判断指定的单元格是否是合并单元格   
      * @param sheet    
      * @param row 行下标   
      * @param column 列下标   
      * @return   
      */   
      private static boolean isMergedRegion(Sheet sheet,int row ,int column) {   
        int sheetMergeCount = sheet.getNumMergedRegions();   
        for (int i = 0; i < sheetMergeCount; i++) {   
        	CellRangeAddress range = sheet.getMergedRegion(i);   
    		int firstColumn = range.getFirstColumn(); 
    		int lastColumn = range.getLastColumn();   
    		int firstRow = range.getFirstRow();   
			int lastRow = range.getLastRow();   
			if(row >= firstRow && row <= lastRow){ 
				if(column >= firstColumn && column <= lastColumn){ 
					return true;   
				} 
			}   
        } 
        return false;   
      } 
     
     /**    
      * 获取单元格的值    
      * @param cell    
      * @return    
      */     
      public static String getCellValue(Cell cell){     
          if(cell == null) return " ";     
          if(cell.getCellType() == Cell.CELL_TYPE_STRING){     
              return cell.getStringCellValue();     
          }else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){     
              return String.valueOf(cell.getBooleanCellValue());     
          }else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){     
              return cell.getCellFormula() ;     
          }else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){     
              return String.valueOf(cell.getNumericCellValue());     
          }     
          return " ";     
      } 

	public static void main(String[] args) throws Exception {
		InputStream is = new FileInputStream(new File("E:\\拍卖成交数据（03—15年）.xlsx"));
		List<List<Object>> list = readExcel(is, "xlsx", 1, 17);
		if (list != null && !list.isEmpty()) {
			List data = new ArrayList();
			// 效验数据...
			StringBuilder infos = new StringBuilder();
			int sucNum = 0;
			if (list != null && !list.isEmpty()) {
				for (int i = 0; i < list.size(); i++) {
					System.out.println("i-----------------" + i);
					List<Object> obj = list.get(i);
					for (Object object : obj) {
						System.out.println(object);
					}
				}
			}
		}
	}
}
